﻿using AspNetCoreRateLimit;
using JinianNet.JNTemplate.Caching;
using JinianNet.JNTemplate.Configuration;
using Microsoft.Extensions.Configuration;
using My.Core.Options;
using My.Framework;
using SqlSugar;
using SqlSugar.IOC;
using Yitter.IdGenerator;

namespace My.Core.SqlSugar
{
    public static class SqlSugarExtension
    {
        private static NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();

        //全部数据权限
        public static string DATA_SCOPE_ALL = "1";
        //自定数据权限
        public static string DATA_SCOPE_CUSTOM = "2";
        //部门数据权限
        public static string DATA_SCOPE_DEPT = "3";
        //部门及以下数据权限
        public static string DATA_SCOPE_DEPT_AND_CHILD = "4";
        //仅本人数据权限
        public static string DATA_SCOPE_SELF = "5";

        public static void AddDb()
        {
            string connStr = AppSettings.GetConfig("ConnectionStrings:conn_db");
            int dbType = Convert.ToInt32(AppSettings.GetConfig("ConnectionStrings:conn_db_type"));

			SnowIdSetting snowIdSetting = App.GetOptions<SnowIdSetting>();

			// 注册雪花Id
			YitIdHelper.SetIdGenerator(snowIdSetting);

            // 自定义 SqlSugar 雪花ID算法
            SnowFlakeSingle.WorkId = snowIdSetting.WorkerId;
            StaticConfig.CustomSnowFlakeFunc = () =>
            {
                return YitIdHelper.NextId();
            };

            var iocList = new List<IocConfig>() {
                   new IocConfig() {
                    ConfigId = "0",//默认db
					ConnectionString = connStr,
                    DbType = (IocDbType)dbType,
                    IsAutoCloseConnection = true
                },
                   new IocConfig() {
                    ConfigId = "1",
                    ConnectionString = connStr,
                    DbType = (IocDbType)dbType,
                    IsAutoCloseConnection = true
                }
			    //...增加其他数据库
			};

            SugarIocServices.AddSqlSugar(iocList);
            ICacheService cache = new SqlSugarCache();
            SugarIocServices.ConfigurationSugar(db =>
            {
                //db0数据过滤
                FilterData(0);
                iocList.ForEach(iocConfig =>
                {
                    SetSugarAop(db, iocConfig, cache);
                });
            });
        }

        private static void SetSugarAop(SqlSugarClient db, IocConfig iocConfig, ICacheService cache)
        {
            var config = db.GetConnection(iocConfig.ConfigId).CurrentConnectionConfig;
			var showDbLog = AppSettings.Get<bool>("ShowDbLog");
			string configId = config.ConfigId;
            
            // 数据库sql语句执行日志
            db.GetConnectionScope(configId).Aop.OnLogExecuting = (sql, pars) =>
            {
                if (showDbLog)
                {
					string log = $"【db{configId} SQL语句】{UtilMethods.GetSqlString(config.DbType, sql, pars)}\n";
					if (sql.TrimStart().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
					{
						logger.Info(log);
					}
					else if (sql.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase) || sql.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase))
					{
						logger.Warn(log);
					}
					else if (sql.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase) || sql.StartsWith("TRUNCATE", StringComparison.OrdinalIgnoreCase))
					{
						logger.Error(log);
					}
					else
					{
						log = $"【db{configId} SQL语句】dbo.{sql} {string.Join(", ", pars.Select(x => x.ParameterName + " = " + GetParsValue(x)))};\n";
						logger.Info(log);
					}
				}
			};

            // 数据错误日志
            db.GetConnectionScope(configId).Aop.OnError = (ex) =>
            {
                Console.ForegroundColor = ConsoleColor.Red;
                string sql = "【错误SQL】" + UtilMethods.GetSqlString(config.DbType, ex.Sql, (SugarParameter[])ex.Parametres) + "\r\n";
                logger.Error(ex, $"{sql}\r\n{ex.Message}\r\n{ex.StackTrace}");
            };

			// 数据审计
			db.GetConnectionScope(configId).Aop.DataExecuting = (oldValue, entityInfo) =>
            {
				if (entityInfo.OperationType == DataFilterType.InsertByObject)
				{
					// 主键(long类型)且没有值的---赋值雪花Id
					if (entityInfo.EntityColumnInfo.IsPrimarykey && entityInfo.EntityColumnInfo.PropertyInfo.PropertyType == typeof(long))
					{
						var id = entityInfo.EntityColumnInfo.PropertyInfo.GetValue(entityInfo.EntityValue);
						if (id == null || (long)id == 0)
							entityInfo.SetValue(YitIdHelper.NextId());
					}
					if (entityInfo.PropertyName == "CreateTime")
						entityInfo.SetValue(DateTime.Now);
				}
				if (entityInfo.OperationType == DataFilterType.UpdateByObject)
				{
					if (entityInfo.PropertyName == "UpdateTime")
						entityInfo.SetValue(DateTime.Now);
				}
			};
            //差异日志功能
            db.GetConnectionScope(configId).Aop.OnDiffLogEvent = it =>
            {

            };
            db.GetConnectionScope(configId).CurrentConnectionConfig.MoreSettings = new ConnMoreSettings()
            {
                IsAutoRemoveDataCache = true    // 自动删除二级缓存?,为true表示可以
            };
            db.GetConnectionScope(configId).CurrentConnectionConfig.ConfigureExternalServices = new ConfigureExternalServices()
            {
                DataInfoCacheService = cache,   // 设置缓存服务
                EntityService = (c, p) =>       // 不想用SqlSugar里面的 实体特性可以用这个自定义实现
                {
                    if (p.IsPrimarykey == true)//主键不能为null
                    {
                        p.IsNullable = false;
                    }
                    else if (p.ExtendedAttribute?.ToString() == ProteryConstant.NOTNULL.ToString()) // 该字段不能为null?
                    {
                        p.IsNullable = false;
                    }
                    else//则否默认为null
                    {
                        p.IsNullable = true;
                    }

                    if (config.DbType == DbType.PostgreSQL)
                    {
                        //if (c.Name == nameof(SysMenu.IsCache) || c.Name == nameof(SysMenu.IsFrame))
                        //{
                        //	p.DataType = "char(1)";
                        //}
                    }
                    #region 兼容Oracle
                    if (config.DbType == DbType.Oracle)
                    {
                        if (p.IsIdentity == true)
                        {
                            //if (p.EntityName == nameof(SysUser))
                            //{
                            //	p.OracleSequenceName = "SEQ_SYS_USER_USERID";
                            //}
                            //else if (p.EntityName == nameof(SysRole))
                            //{
                            //	p.OracleSequenceName = "SEQ_SYS_ROLE_ROLEID";
                            //}
                            //else if (p.EntityName == nameof(SysDept))
                            //{
                            //	p.OracleSequenceName = "SEQ_SYS_DEPT_DEPTID";
                            //}
                            //else if (p.EntityName == nameof(SysMenu))
                            //{
                            //	p.OracleSequenceName = "SEQ_SYS_MENU_MENUID";
                            //}
                            //else
                            //{
                            //	p.OracleSequenceName = "SEQ_ID";
                            //}
                        }
                    }
                    #endregion
                }
            };
            db.GetConnectionScope(configId).Aop.OnLogExecuted = (sql, pars) =>
            {
                var sqlExecutionTime = AppSettings.Get<int>("sqlExecutionTime");
                if (db.Ado.SqlExecutionTime.TotalSeconds > sqlExecutionTime)
                {
                    //代码CS文件名
                    var fileName = db.Ado.SqlStackTrace.FirstFileName;
                    //代码行数
                    var fileLine = db.Ado.SqlStackTrace.FirstLine;
                    //方法名
                    var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                    var logInfo = $"Sql执行超时，用时{db.Ado.SqlExecutionTime.TotalSeconds}秒【{sql}】,fileName={fileName},line={fileLine},methodName={FirstMethodName}";
                    //WxNoticeHelper.SendMsg("Sql请求时间过长", logInfo);
                    logger.Warn(logInfo);
                }
            };
        }

        /// <summary>
        /// 数据过滤
        /// </summary>
        /// <param name="configId">多库id</param>
        private static void FilterData(int configId)
        {

        }

		private static object GetParsValue(SugarParameter x)
		{
			if (x.DbType == System.Data.DbType.String || x.DbType == System.Data.DbType.DateTime || x.DbType == System.Data.DbType.String)
			{
				return "'" + x.Value + "'";
			}
			return x.Value;
		}
	}
}
